﻿Imports System.Data.SqlServerCe

Public Class Environment
    Private Shared mPassword As String
    Private Shared mFilePath As String
    Public Shared Sub SetActiveDatabase(ByVal filePath As String, ByVal password As String)
        mFilePath = filePath
        mPassword = password
    End Sub

    Public Shared Function TestConnection(ByVal filePath As String, ByVal password As String) As Boolean
        If Not IO.File.Exists(filePath) Then Return False
        Dim strVersion As String = MetaData.GetDBVersion(filePath, password)
        Return Not String.IsNullOrEmpty(strVersion)
    End Function

    Friend Shared Function GetConnectionString(ByVal filePath As String, ByVal password As String) As String
        Return String.Format("Data Source={0};Password={1};", filePath, password)
    End Function

    Friend Shared Function GetConnection() As SqlCeConnection
        Return GetConnection(DataSource.Local)
    End Function
    Friend Shared Function GetConnection(ByVal source As DataSource) As SqlCeConnection
        Select Case source
            Case DataSource.Local
                Return New System.Data.SqlServerCe.SqlCeConnection(GetConnectionString(mFilePath, mPassword))
            Case DataSource.Template
                Return New System.Data.SqlServerCe.SqlCeConnection(GetConnectionString(TemplateDBPath, TemplateDBPassword))
            Case Else
                Throw New NotImplementedException
        End Select
    End Function
    Friend Shared Function GetConnection(ByVal filePath As String, ByVal password As String) As SqlCeConnection
        Return New System.Data.SqlServerCe.SqlCeConnection(GetConnectionString(filePath, password))
    End Function

    Friend Shared Function NewCommand(ByVal sql As String) As SqlCeCommand
        Return New SqlCeCommand(sql, GetConnection)
    End Function
    Friend Shared Function NewCommand(ByVal sql As String, ByVal source As DataSource) As SqlCeCommand
        Return New SqlCeCommand(sql, GetConnection(source))
    End Function
    Friend Shared Function NewCommand(ByVal sql As String, ByVal connection As SqlCeConnection) As SqlCeCommand
        Return New SqlCeCommand(sql, connection)
    End Function
    Friend Shared Function NewCommand(ByVal sql As String, ByVal transactionScope As TransactionScope) As SqlCeCommand
        Dim clsRet As New SqlCeCommand(sql, transactionScope.Connection)
        clsRet.Transaction = transactionScope.Transaction
        Return clsRet
    End Function
    Friend Shared Function NewCommand(ByVal sql As String, ByVal filePath As String, ByVal password As String) As SqlCeCommand
        Return New SqlCeCommand(sql, GetConnection(filePath, password))
    End Function

    Public Shared Sub CreateLocalDatabase(ByVal path As String, ByVal password As String)
        If IO.File.Exists(path) Then IO.File.Delete(path)

        'create database file
        Dim strConn As String = GetConnectionString(path, password)
        Using clsEngine As New SqlCeEngine(strConn)
            clsEngine.CreateDatabase()
        End Using

        mFilePath = path
        mPassword = password

        'create the metadata table
        Dim strSql1 As String = "CREATE TABLE MetaData ([Key] nvarchar(100) NOT NULL PRIMARY KEY,Value nvarchar(256))"
        ExecuteNonQuery(strSql1, DataSource.Local)

        Dim strSql2 As String = "INSERT INTO MetaData VALUES ('VERSION','0.0')"
        ExecuteNonQuery(strSql2, DataSource.Local)
    End Sub

    Public Shared Function GetColumnInfos_Template(ByVal tableName As String) As Generic.List(Of ColumnInfo)
        Using t As New TransactionScope(DataSource.Template)
            Return GetColumnInfos(tableName, t)
            t.Complete()
        End Using
    End Function

    Public Shared Function GetColumnInfos(ByVal tableName As String, ByVal transactionScope As TransactionScope) As Generic.List(Of ColumnInfo)
        Dim strSql As String = "select column_name,ordinal_position,is_nullable,data_type,character_maximum_length,autoinc_increment,autoinc_seed " & _
                               "from information_schema.columns " & _
                               "where table_name = @TableName " & _
                               "order by ordinal_position"

        Dim sqlCmd As SqlCeCommand = NewCommand(strSql, transactionScope)
        sqlCmd.Parameters.Add("@TableName", tableName)
        Using dr As SqlCeDataReader = sqlCmd.ExecuteReader
            Dim aRet As New Generic.List(Of ColumnInfo)
            While dr.Read
                Dim clsCol As New ColumnInfo
                clsCol.Name = dr.GetString(0)
                clsCol.OrdinalNr = dr.GetInt32(1)
                clsCol.IsNullable = (dr.GetString(2) <> "NO")
                clsCol.DataType = dr.GetString(3)
                If Not dr.IsDBNull(4) Then clsCol.CharMaxLen = dr.GetInt32(4)
                If Not dr.IsDBNull(5) Then clsCol.AutoInc_Increment = dr.GetInt64(5)
                If Not dr.IsDBNull(6) Then clsCol.AutoInc_Seed = dr.GetInt64(6)
                aRet.Add(clsCol)
            End While
            Return aRet
        End Using
    End Function
    Public Shared Function GetIndexInfos_Template(ByVal tableName As String) As IndexInfo()
        Dim strSql As String = "select index_name,primary_key,[unique],ordinal_position,column_name " & _
                                "from information_schema.indexes " & _
                                "where table_name = @TableName " & _
                                "order by index_name,ordinal_position"


        Dim sqlCmd As SqlCeCommand = NewCommand(strSql, DataSource.Template)
        sqlCmd.Parameters.Add("@TableName", tableName)
        sqlCmd.Connection.Open()
        Try
            Using dr As SqlCeDataReader = sqlCmd.ExecuteReader
                Dim dIdx As New Generic.Dictionary(Of String, IndexInfo)
                While dr.Read
                    Dim strIdxName As String = dr.GetString(0)
                    Dim clsIdx As IndexInfo
                    If dIdx.ContainsKey(strIdxName) Then
                        clsIdx = dIdx(strIdxName)
                    Else
                        clsIdx = New IndexInfo
                        clsIdx.Name = strIdxName
                        dIdx.Add(strIdxName, clsIdx)
                    End If
                    clsIdx.IsPrimaryKey = dr.GetBoolean(1) ' (dr.GetInt32(1) = 1)
                    clsIdx.IsUnique = dr.GetBoolean(2) ' (dr.GetInt32(2) = 1)
                    clsIdx.ColNames.Add(dr.GetString(4))
                End While
                Return dIdx.Values.ToArray
            End Using
        Finally
            sqlCmd.Connection.Close()
        End Try
    End Function
    Public Class IndexInfo
        Public Name As String
        Public IsPrimaryKey As Boolean
        Public IsUnique As Boolean
        Public ColNames As New Generic.List(Of String)
    End Class

    Public Class ColumnInfo
        Public Name As String
        Public OrdinalNr As Integer
        Public IsNullable As Boolean
        Public DataType As String
        Public CharMaxLen As Integer
        Public AutoInc_Increment As Long
        Public AutoInc_Seed As Long
    End Class

    Public Shared ReadOnly Property DBPath() As String
        Get
            Return mFilePath
        End Get
    End Property
    Public Shared ReadOnly Property DBPassword() As String
        Get
            Return mPassword
        End Get
    End Property

    Friend Shared ReadOnly Property TemplateDBPath() As String
        Get
            Return IO.Path.Combine(System.Environment.CurrentDirectory, "DatabaseTemplates\Client.sdf")
        End Get
    End Property
    Friend Shared ReadOnly Property TemplateDBPassword() As String
        Get
            Return "b2b"
        End Get
    End Property

    Public Shared Function ExecuteNonQuery(ByVal command As SqlCeCommand) As Integer
        If command.Transaction Is Nothing Then command.Connection.Open()
        Try
            Return command.ExecuteNonQuery()
        Finally
            If command.Transaction Is Nothing Then command.Connection.Close()
        End Try

    End Function
    Public Shared Function ExecuteNonQuery(ByVal statement As String) As Integer
        Return ExecuteNonQuery(statement, DataSource.Local)
    End Function

    Public Shared Function ExecuteNonQuery(ByVal statement As String, ByVal source As DataSource) As Integer
        Dim sqlCmd As SqlCeCommand = NewCommand(statement, source)
        Return ExecuteNonQuery(sqlCmd)
    End Function

    Public Shared Function ExecuteNonQuery(ByVal statement As String, ByVal connection As SqlCeConnection) As Integer
        Dim sqlCmd As SqlCeCommand = NewCommand(statement, connection)
        Return sqlCmd.ExecuteNonQuery()
    End Function

    Public Shared Function ExecuteNonQuery(ByVal statement As String, ByVal transactionScope As TransactionScope) As Integer
        Dim sqlCmd As SqlCeCommand = NewCommand(statement, transactionScope)
        Return ExecuteNonQuery(sqlCmd)
    End Function

    Public Shared Function ExecuteCommandToStringList(ByVal statement As String, Optional ByVal source As DataSource = DataSource.Local, Optional ByVal columnIndex As Integer = 0) As Generic.List(Of String)
        Dim sqlCmd As SqlCeCommand = Environment.NewCommand(statement, source)
        sqlCmd.Connection.Open()
        Try
            Using dr As SqlCeDataReader = sqlCmd.ExecuteReader()
                Dim aRet As New Generic.List(Of String)
                While dr.Read
                    aRet.Add(dr.GetString(columnIndex))
                End While
                Return aRet
            End Using
        Finally
            sqlCmd.Connection.Close()
        End Try

    End Function


End Class

Public Enum DataSource
    Local
    Template
End Enum
